The Match window has option switches which allow the record number, current
key and subfile number to be included in a report. There used to be no
choice about where these extra items appeared or in what order: the order
was fixed to that stated above and the information appeared in the report at
the beginning of each record. It is now possible to incorporate these items
anywhere in the field order. With the Match window open, highlight fields
for printing by clicking with ADJUST in the usual way and select the option
switches at the required places in the field order.
The field-selection menu, accessed from a menu button on the Match window
and also by Ctrl F (see 3.4, M), shows these extra items for printing in
their correct place in the field order, picking them out in dark blue. If
Extra calculations have been defined (see 6, S) these also appear on the
selection menu in red. In view of the increased importance of this menu it
is now also accessible via a new entry, Display selection, on the Print
submenu.
3.10 The Print options window
-----------------------------
A new icon next to the field for the Sort on field tag or column number
allows you to specify whether the pre-display sort is to be ascending or
descending. It shows a blue arrow which may be clicked to toggle it between
an up and a down arrow.
New options have been introduced for the printing of scrollable lists. When
printing in the default single row format items from the same row of the
scrollable list were formerly separated by a single space and the end of
each row marked by a semicolon followed by a space. Both these markers can
now be chosen by the user by entering them into two writable icons. The
supplied defaults are a comma-plus-space and a semicolon-plus-two-spaces
respectively. When the alternative columnar format is chosen the end-of-row
marker is irrelevant and is greyed out, but the separator used between items
in the same row may still be chosen. A comma can look ugly in this format
and one or more spaces could be a more suitable choice. (See also 8.3.1, S).
4.2.1 Simple field creation
---------------------------
The choices for the layout grid can now be saved and will be used next time
you run Powerbase. You can return to the settings initially supplied by
Shift-clicking with SELECT on Load defaults. (see also p.2., S, “Preserving
your saved options when upgrading”)
4.2.5 Other types of editable field
-----------------------------------
Entering an 8-character date (i.e. one with a 2-digit year) into a
10-character date field (one which displays the year in full) now handles
century wrap-around as follows:
(a) Years less than 10 are assumed to be 21st century, e.g. 19-10-02 will be
reformatted as 19-10-2002
(b) Other years are assumed to be 20th century, e.g. 19-10-42 will be
reformatted as 19-10-1942
The century changeover year can be altered by editing
!Powerbase.Resources.Config. Look for the token CentChange and alter the
associated value (currently 10) to whatever you require. The above rule is
also followed when a database is reformatted so that an 8-character date
becomes a 10-character one (see 10.3, S).
4.2.6 Scrollable lists
-----------------------
Most of the material on p.30 of the manual relating to this section is now
superseded. The definition of these fields has been made much easier. It is
no longer necessary to enter the column width in Data length (in fact this
icon is greyed out when you select Scrollable list as the field type), nor
do you need to create a Format file in a separate operation in order to
specify columns of differing width. An extension at the foot of he
field-definition window allows you to enter the required widths for the
number of columns chosen, and also the number of visible rows.
Moreover, you may change the column widths (and even the number of columns)
without rebuilding the whole database as you would need to do if you wanted
to alter the defined length for other types of field. The Adjust format
utility from the iconbar menu allows you to make such changes with the
minimum amount of bother. Just one word of warning: if you increase the
number of columns in a Scrollable list by this method the data will spread
out over the new columns, i.e. the cells will be used in order. If what you
really want is one or more blank columns to the right of the existing data
then you need New database format, not Adjust format.
4.2.8 External fields
---------------------
A new option switch, labelled Display, has been added to the
Field-definition window. It is active only for External fields of types
Text, Sprite, Draw and Remote, i.e. those which link external objects to
buttons on the record window.
When a record is displayed, any field for which this switch is selected will
behave as though the button had been clicked. You could, for instance, have
a Remote button linking records to a collection of JPEGs and the pictures
would be displayed as you browsed through the database without any further
action on your part. For this to work a suitable JPEG viewer must have been
‘seen’ by the filer. Most users will have ChangeFSI, recent versions of
which can display JPEGs, but it is rather slow. Thomas Olsson's PD
application EasyView is much faster and ideal for using with Powerbase in
this way. Draw can, from RISC OS 3.7 on, display JPEGs with lightning speed
but is no use for the purpose described above because it doesn't respond to
double-clicks on JPEGs or Filer_Open commands issued by Powerbase for JPEGs.
Note: This auto-display feature can also be toggled on and off by clicking Shift-SELECT on the relevant button, irrespective of whether the new option switch is selected. If you wanted the feature to be active when the database was opened you would set the option switch, which would still leave you with the ability to turn auto-display off temporarily using Shift-SELECT. If, on the other hand, you only want to use auto-display occasionally, leave the option switch off and turn the feature on with Shift-SELECT when you require it.
Important update on Remote type
Previous versions of Powerbase stored the absolute pathnames of objects
linked to Remote buttons. v.8.27 also allows relative pathnames to be used,
i.e. relative to the position of the database in the disc’s directory
structure. Both methods have their advantages and disadvantages and the type
of pathname stored is determined by the entry PathLen in the Config file. This
item formerly specified only the maximum path-length for Remote objects, the
default being 255. The number is now suffixed with either R (for relative) or
A (for absolute), the former being the default. Individual databases can
have their own Config files so it is quite feasible for some databases to use
one type of pathname and some the other.
Absolute pathnames are suitable when you are sure the files associated with
the database are going to stay in the same locations as when they were first
attached to the records, but you might want to move the database itself. You
can move the database to a different disc, or even a different filing system
(on the same machine), and the Remote button will still work. Moving one of
the attached files, however, will make the stored pathname invalid and cause
the button to show a ‘?’ instead of the appropriate file icon.
It is envisaged that users will want to use databases with Remote fields to
catalogue data such as collections of photographs in JPEG format and might
at some stage wish to move the whole collection to a zip drive or CD ROM.
The use of relative pathnames makes it possible.
An example will make this clearer. Suppose we have a directory of JPEGs
called Pictures, each of which is associated via a Remote button with a
record in a database called !MyPhotos. The arrangement on the hard disc
which is what previous versions of Powerbase would have stored. The pathname
of the database is:
ADFS::HardDisc4.$.FredsData.Databases.!MyPhotos
The first sections of both pathnames, as far as and including FredsData, are
identical and this enables us to locate Horse without using its full
pathname. Powerbase does, of course, know the full pathname for !MyPhotos
when the database is open so it can find Horse by, in effect, saying: “Go up
two levels in the disc tree from the database, then use the pathname
Pictures.Animals.Horse ”.
But how do we let Powerbase know it needs to go up two levels? The character
‘^’, included as an element in a pathname, is an instruction to go up one
level. What needs to be stored for the example given above therefore is:
^.^.Pictures.Animals.Horse
which is the pathname relative to !MyPhotos. When Powerbase reads this all
it needs to do is prefix it with the already-known pathname for !MyPhotos and
the JPEG Horse can be retrieved. If the directories Databases (containing
!MyPhotos) and Pictures (containing the subdirectory Animals) were
transferred to the root directory of a CD ROM the pathnames of Horse and
!MyPhotos might change to something like:
CDFS::PICLIB.$.Pictures.Animals.Horse
and: CDFS::PICLIB.$.Databases.!MyPhotos
If !MyPhotos contained the (old) absolute pathname for Horse, Powerbase
would be unable to find the file, but the relative pathname is still valid:
it’s still a matter of going up two directories (thus reaching the root) and
then working down from there.
You can highlight a Remote button with ADJUST as part of a print selection in order to print the stored pathnames of the Remote objects. An individual pathname may be displayed by clicking with Shift-ADJUST.
Attention is also drawn to the fact that objects attached to Run file and
Directory buttons on the record window have their pathnames determined in
the same way as for Remote objects. (See below.)
4.2.11 Button fields
--------------------
Run file and Directory
To avoid problems with objects linked to Run file and Directory buttons when
a database is moved to a different disc, filing system or computer, make sure
that the linked files are referenced by relative rather than absolute
pathnames. (See 4.2.8 S, above.) If you are using such button fields you are
recommended to re-link the object to the button.
It must be said, however, that the best policy of all is to put files which
are of significance to one database only into the database’s PrintRes
directory. Only the leafname of the file is stored in this case and, on
finding only a leafname, Powerbase looks for the file in PrintRes.
Menu
The link between a pop-up menu button and the field into which the menu
enters data was formerly established by using the field-number of the data
field as the tag of the menu button. This has now been changed so that the
actual tag of the relevant data field is used instead of the field-number.
i.e. the data field and the menu button have the same tag − the only
circumstance in which this is permitted (apart from label text which has a
null tag). If you do enter the field-number you will find on returning to
the field definition that the tag has been substituted. User menus on older
databases which actually do use the field-number as the button tag will
still work correctly.
The procedure for creating and editing the text file for a user menu has
also been improved. When the menu button is clicked with SELECT or MENU for
the first time the default text file is displayed in an editor and contains
instructions for tailoring it to your requirements. You then save the file
under the same name and subsequent clicks on the menu button open the menu.
Should you need to edit the menu file again later you can call it up by
clicking the menu button with ADJUST. These changes make it unnecessary for
users to delve into the Menus directory inside the database.
By default user menus pop up whenever the caret enters the associated data
field. This action can be turned off via a new switch on the Preferences
window.
5.5 Displaying validation tables
--------------------------------
Double-clicking a linked data field to bring up the relevant validation
table entry now works for Scrollable list fields, as does the automatic
display of linked data when the Display linked table data switch on the
Preferences window is selected.
5.9 Entering validation table data into a record
------------------------------------------------
The ability to enter data into a field by double-clicking with SELECT on an
item in a validation table has now been extended. You can enter data into
Scrollable list fields in this way. Moreover, the caret moves automatically
to the next cell on the assumption that you are probably entering several
items from the table into the list. On reaching the last cell, holding down
Shift as you double-click will add a new row to the list, making this form
of input consistent with direct typing into the list.
Two further improvements have been made to the use of validation tables with
Scrollable list fields. Coded data will be expanded in reports if the Expand
codes switch is selected in Print options, and the Replace-on-entry feature
also works, provided the replacement string will fit the cell of the
Scrollable list.
It is also possible to enter data from a validation table into other
writable icons on Powerbase dialogue boxes using this method. It could be
especially useful as an aid to entering search formulae.
6 Performing calculations
-------------------------
Computed fields, containing the results of calculations and string
operations on other fields (and also the results of User Functions) have
been present in Powerbase for a long time now. They do, however, have the
disadvantage of requiring the user either to foresee at the time the
database is designed what calculations will be required or reformat later in
order to include a field which might only be needed for a one-off inclusion
in a report.
v. 8.20 lets you specify such calculations without the need for a field to
which to attach them. In a sense they resemble the existing column
calculations for mean, standard deviation etc. in that they are performed on
the fly during the creation of a report.
To define a calculation choose the new entry Extra calculations (Ctrl Shift
N) from the Print submenu. The window which appears requires you to enter a
formula, which has exactly the same format as the formulae attached to
Calculated and Composite fields (see 6.1 and 6.2, M). Since the calculation
will not be attached to a field from which to derive a heading, a width and
whether the result is to be numeric or not, you must supply this data
yourself. If you omit anything Powerbase will use the formula itself as a
heading, assume a Numeric result and assign a width of 15 characters (the
maximum space a number can occupy). If you have clicked the radio button to
select a String (i.e. non-numeric) result such as would be placed in a
Composite field, the width will default to 255 characters. This might seem
an enormous value, but unnecessary “white space” will be removed from the
report before printing if the output is to the printer. If the report is to
be displayed in a window the Shrink list button on the Print options window
should be selected. Failing this you may remove the surplus space by
choosing Shrink list from the menu over the report window.
It is possible to produce errors by unsuitable combinations of fields and
result types (just as it is for Computed fields). The main thing to remember
is that Numeric fields will normally produce a numeric result so assigning a
String operation to such fields can be expected to produce an error. There
are exceptions even to this rule since the Basic STR$() function may be used
to convert a number to a string, thus allowing Numeric fields to give a
String result. Dates, Times and User Functions may be used in Extra
calculations, just as they can in Computed fields.
When you have defined your calculation, click on Include. The displayed
calculation number will go from 0 to 1 and the window is set up for entry of
another calculation. A total of 10 (numbered 0-9) are allowed and you may
return to earlier calculations to edit them by clicking the “bump” icons.
The position of such calculated results in the report is determined by the
point at which you define them. If you click ADJUST to highlight a field,
then type Ctrl Shift N to bring up the new window and define a calculation,
and finally highlight another field, the calculated result will appear
between the highlighted fields. The menu of selected fields (see 3.7.2, S)
shows these calculations in red so you can easily check on the order in
which things will appear on the report. You can choose the red entries from
this menu to bring up the calculation window for editing.
All defined calculations are saved as part of a Print Selection and will be
restored when the Selection file is subsequently reloaded. The Clear button
on the calculation window has the same effect as Clear selection (Ctrl Z)
from the Print submenu.
6.3 User functions
------------------
The management of User functions has been improved. Previous versions of Powerbase kept all user functions in a single Basic file called UserFuncs, which was placed directly inside the database directory and loaded as a library when the database was opened. This could be somewhat inconvenient. If the user wanted to use a function from the supplied UserFuncs file s/he could either place the entire file inside the database, leading to a waste of memory, or extract the required function and save it separately in the database directory as UserFuncs.
From v.8.24 UserFuncs is a directory and each function is saved in it as a separate Basic program file which may be called anything the user wishes. When the database is opened all such files, whether there’s one or a dozen, are loaded. Individual databases therefore need only store the User functions they actually use and copying individual functions from one database to another is easy.
If, on opening a database, it is found to contain an old-style UserFuncs file, as opposed to a directory, the missing directory is created and the file transferred to it. It will not, however, be split into separate functions.
7.2 Indexing a field
--------------------
All dates and date-related fields such as day-of-week, month-of-year now
index chronologically.
8.3 Exporting data as a CSV file
--------------------------------
CSV export has been harmonised with printing in two ways. If there is a
default print selection file (i.e. one called !Selection in PrintRes) it
will be used to determine what fields are exported provided no alternative
selection has been highlighted. If you hold down Shift when initiating a CSV
export operation only the currently-displayed record will be exported.
8.3.1 Scrollable lists and CSV files
------------------------------------
When you include a scrollable list as one of the fields in a CSV file it
will be written as a single CSV field if the single row format is selected
in the Print options window. The data from individual rows of the list was
formerly separated by a semicolon but this is now configurable by a new
choice Scroll term in the CSV options window underneath the Separator and
Terminator for CSV fields. The default for Scroll term is still a semicolon.
The data from individual cells in the same row of the scrollable list are
separated by the same CSV separator as is used between CSV fields.
The above description might seem confusing but will become clearer if you
export data from the sample database Scroller using the default settings but
with In quotes selected in CSV options. You will see that the entire
scrollable list from each record is enclosed in double quotes, between which
the items from within each row are separated by commas, a semicolon marking
the end of each row.
A file exported in this way can be re-imported into a Powerbase database.
You must ensure that the scrollable list data goes back into a scrollable
list field, although it need not have the same number of columns as the one
from which it was exported. If the new list has more columns than the old
there will be one or more blank columns on the right hand side; if there are
fewer columns data from the missing columns will be discarded. This handling
of data underflow and data overflow is the same as what happens when there
are more or fewer fields in the the database than in the one from which the
file was exported (see 8.4.2, M).
If you change the CSV printing format in Print options from As single row to
As columns and export the CSV file again (in quotes as before) you will see
the contents of each row enclosed in double quotes and separated by commas,
the items from within each row being also separated by commas.
Exporting this second file without the quotes (or the first file if you
first change Scroll term to a comma) will result in a file in which every
single cell of the scrollable list appears as a separate CSV field,
separated from the next by a comma. This might prove useful if transferring
data from a database which contains a scrollable list to one which doesn’t.
8.6 Creating a new, working database from a CSV file
